package com.gzx.bi.modular.team.mapper;

import com.gzx.bi.modular.team.bo.GzxTeamEffectBo;
import com.gzx.bi.modular.team.entity.GzxTeamEffect;
import com.gzx.bi.modular.team.vo.GzxTeamEffectVo;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import vip.xiaonuo.common.mapper.BaseMapperPlus;

import java.util.List;

/**
 * 团队效率
 */
public interface GzxTeamEffectMapper extends BaseMapperPlus<GzxTeamEffect, GzxTeamEffectVo> {

    //插入存在则更新
    @Insert({
            "<script>",
            "insert into gzx_team_effect(callout_num,connect_num,connect_duration,effect_duration,`current_time`,belong_project,belong_team,agent_num) "+
                    " values(#{calloutNum},#{connectNum},#{connectDuration},#{effectDuration},DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i'),#{belongProject},#{belongTeam},#{agentNum}) "+
                    " ON DUPLICATE KEY UPDATE "+
                     " callout_num = values(callout_num) + #{calloutNum} , connect_num = values(connect_num) + #{connectNum}, connect_duration = values(connect_duration) + #{connectDuration} , effect_duration = values(effect_duration) + #{effectDuration}"+
                    "</script>"
    })
    void insertUpdate(GzxTeamEffect team);

    //团队效率
    @Select({
            "<script>",
            "SELECT "+
            " pd.project_name projectName,"+
            " pa.attend_user attendUser,"+
            " pd.debt_user debtUser,"+
            " pd.case_load caseLoad,"+
            " (pd.case_load/pd.debt_user) avgCase,"+
            " sum(te.callout_num) calloutNum,"+
            " sum(te.connect_num) connectNum,"+
            " sum(te.connect_duration) connectDuration,"+
            " sum(te.effect_duration) effectDuration,"+
            " (sum(te.callout_num)/pd.debt_user) avgCallout,"+
            " (sum(te.connect_duration)/pd.debt_user) avgConnectDuration,"+
            " (sum(te.effect_duration)/pd.debt_user) avgEffectDuration,"+
            " (sum(te.connect_num)/sum(te.callout_num)) rateConnect,"+
            " (pd.debt_user/pa.attend_user) rateActive,"+
            " (sum(pd.debt_user)/sum(pa.attend_user)) rateAvgActive"+
            " FROM gzx_team_effect te INNER JOIN gzx_project_debt pd ON te.belong_project = pd.project_code"+
            " LEFT JOIN gzx_project_attendance pa ON te.belong_project = pa.project_code"+
            "<if test='currentTime != null and currentTime != \"\"'> WHERE te.`current_time` like concat(DATE_FORMAT(#{currentTime}, '%Y-%m-%d') ,'%') </if>" +
            "<if test='belongProject != null and belongProject != \"\"'> AND te.belong_project in ${belongProject} </if>" +
            "<if test='agentNum != null and agentNum != \"\"'> AND te.agent_num = #{agentNum} </if>" +
            " GROUP BY te.belong_project,pa.attend_user,pd.debt_user,pd.case_load,pd.project_name "+
            " ORDER BY avgCallout desc"+
            "</script>"
    })
    List<GzxTeamEffectVo> getTeamEffect(GzxTeamEffectBo bo);
}
